package org.dusin.web.util;


import java.io.FileInputStream;
import java.io.IOException;


import java.util.List;


import java.io.OutputStream;

import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

    public ExcelUtil() {
    }

    private static HSSFWorkbook readFile(String filename) throws IOException {
		return new HSSFWorkbook(new FileInputStream(filename));
    }
    
    
    /**
     * @param list,写入文件的数据
     * @param fields，写入的字段
     * @param out
     * @param filename，载入的模板文件
     * @param startRow，起始行号
     * @param startCol，起始列号
     * @throws IOException 
     */
    public void getExcelFileFromTemplate(List<Map<String,Object>> list,String[] fields,OutputStream out,String filename,int startRow,int startCol) throws IOException{
        Workbook wb = readFile(filename);
        Sheet sheet1 = wb.getSheetAt(0);
        CreationHelper createHelper = wb.getCreationHelper();
        CellStyle cellStyle = wb.createCellStyle();
         cellStyle.setDataFormat(
         createHelper.createDataFormat().getFormat("0.000"));
         
         CellStyle cellStyleDate = wb.createCellStyle();     //日期格式
         cellStyleDate.setDataFormat(
         createHelper.createDataFormat().getFormat("yy-m-d h:mm:ss"));
        
        for(int i=0;i<list.size();i++){
            Map<String,Object> map = list.get(i);
            //Row row = sheet1.createRow(startRow+i);
            Row row = sheet1.createRow(startRow+i);            
            for(int j=0;j<fields.length;j++){
                
                Object value = map.get(fields[j]);
                Cell cell = row.createCell(startCol+j);
                if("rate".equals(fields[j])||"upperLimitRate".equals(fields[j])||"lowerLimitRate".equals(fields[j])){
                    cell.setCellStyle(cellStyle);
                }/*else if("minVoltageTime".equals(fields[j])||"minVoltageTime".equals(fields[j])){
                    cell.setCellStyle(cellStyleDate);
                }*/
                
                if (value != null) {
                    if(value instanceof Integer){
                        cell.setCellValue((Integer)value);
                    }else if(value instanceof Double){
                        cell.setCellValue((Double)value);
                    }else if(value instanceof Date){
                        cell.setCellValue((Date)value);
                    }else if (value instanceof BigDecimal){
                        cell.setCellValue(((BigDecimal)value).doubleValue());
                    }
                    else{
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }                
        }
        wb.write(out);
    }
    
    /**
     * @param title,标题
     * @param list,写入文件的数据
     * @param fields，写入的字段
     * @param out
     * @param filename，载入的模板文件
     * @param startRow，起始行号
     * @param startCol，起始列号
     * @throws IOException 
     */
    public void getExcelFileFromTemplate(String title,List<Map<String,Object>> list,String[] fields,OutputStream out,String filename,int startRow,int startCol) throws IOException{
        Workbook wb = readFile(filename);
        Sheet sheet1 = wb.getSheetAt(0);
        CreationHelper createHelper = wb.getCreationHelper();
        CellStyle cellStyle = wb.createCellStyle();
         cellStyle.setDataFormat(
         createHelper.createDataFormat().getFormat("0.000"));
         
         CellStyle cellStyleDate = wb.createCellStyle();     //日期格式
         cellStyleDate.setDataFormat(
         createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
         
         if(title!=null){
            Cell titleCell = sheet1.getRow(0).getCell(0);
            //Cell titleCell = sheet1.createRow(0).createCell(0);
            titleCell.setCellValue(title);
         }
        
        for(int i=0;i<list.size();i++){
            Map<String,Object> map = list.get(i);
            //Row row = sheet1.createRow(startRow+i);
            Row row = sheet1.createRow(startRow+i);            
            for(int j=0;j<fields.length;j++){
                
                Object value = map.get(fields[j]);
                Cell cell = row.createCell(startCol+j);
                if("rate".equals(fields[j])||"upperLimitRate".equals(fields[j])||"lowerLimitRate".equals(fields[j])){
                    cell.setCellStyle(cellStyle);
                }/*else if("minVoltageTime".equals(fields[j])||"minVoltageTime".equals(fields[j])){
                    cell.setCellStyle(cellStyleDate);
                }*/
                
                if (value != null) {
                    if(value instanceof Integer){
                        cell.setCellValue((Integer)value);
                    }else if(value instanceof Double){
                        cell.setCellValue((Double)value);
                    }else if(value instanceof Date){
                        cell.setCellValue((Date)value);
                        cell.setCellStyle(cellStyleDate);
                    }else if (value instanceof BigDecimal){
                        cell.setCellValue(((BigDecimal)value).doubleValue());
                    }
                    else{
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }                
        }
        wb.write(out);
    }
    
    public void getExcelFile(String title, List list, String[] heads, String[] fields, OutputStream out) throws IOException {
        Workbook wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("sheet1");
        Font font = wb.createFont();
        font.setBoldweight((short) 10);
        font.setFontHeightInPoints((short) 24);
        CellStyle cellStyle = wb.createCellStyle();//��������ʽ
        cellStyle.setFont(font);
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);

        CellStyle headStyle = wb.createCellStyle();//��ͷ��ʽ
        Font headFont = wb.createFont();
        headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headStyle.setFont(headFont);
        headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        headStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headStyle.setBorderTop(CellStyle.BORDER_THIN);
        headStyle.setBorderRight(CellStyle.BORDER_THIN);

        CellStyle dataStyle = wb.createCellStyle();
        dataStyle.setBorderBottom(CellStyle.BORDER_THIN);
        dataStyle.setBorderLeft(CellStyle.BORDER_THIN);
        dataStyle.setBorderTop(CellStyle.BORDER_THIN);
        dataStyle.setBorderRight(CellStyle.BORDER_THIN);

        Row row = sheet1.createRow((short) 0);
        Cell cell = row.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(cellStyle);

        //Object obj = list.get(0);
        int columns = heads.length;
        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, columns - 1));

        //��ͷ
        int i = 0;
        Row row1 = sheet1.createRow(1);
        for (String headTitle : heads) {
            Cell titleHead = row1.createCell(i);
            titleHead.setCellStyle(headStyle);
            titleHead.setCellValue(headTitle);
            i++;
        }

        i = 2;
        for (Object obj : list) {
            Row row2 = sheet1.createRow(i);
            int j = 0;
            for (String field : fields) {

                String value=null;
                try {
                    value = BeanUtils.getProperty(obj, field);
                } catch (IllegalAccessException ex) {
                    
                } catch (InvocationTargetException ex) {
                    
                } catch (NoSuchMethodException ex) {
                    
                }
                
                Cell dataCell = row2.createCell(j);
                dataCell.setCellStyle(dataStyle);
                if (value != null) {
                    dataCell.setCellValue(value);
                } else {
                    dataCell.setCellValue("");
                }
                j++;
            }
            i++;
        }
        for (int k = 0; k < columns; k++) {
            sheet1.autoSizeColumn(k,true);
            
        }
        wb.write(out);
    }
}
